<?php

class CompanyRatings extends \Phalcon\Mvc\Model
{

    /**
     *
     * @var integer
     */
    public $id;

    /**
     *
     * @var integer
     */
    public $company_id;

    /**
     *
     * @var integer
     */
    public $user_id;

    /**
     *
     * @var string
     */
    public $reviews;

    /**
     *
     * @var double
     */
    public $rate;

    /**
     *
     * @var string
     */
    public $date_created;

    /**
     * Independent Column Mapping.
     */
    public function columnMap()
    {
        return array(
            'id' => 'id', 
            'company_id' => 'company_id', 
            'user_id' => 'user_id', 
            'reviews' => 'reviews', 
            'rate' => 'rate', 
            'date_created' => 'date_created'
        );
    }

    public function beforeValidationOnCreate()
    {
        $this->date_created = CURR_DATE;
        if(!$this->reviews) $this->reviews = strip_tags($this->reviews);
    }
    public function getAvg($company_id)
    {
        $phql = "SELECT AVG(cr.rate) as avg
                 FROM CompanyRatings cr
                 WHERE company_id = ?0
                 ORDER BY cr.date_created DESC";
        $data = $this->modelsManager->executeQuery($phql,array(0=>$company_id));
        return $data[0]->avg;   
    }

    public function countReviews($company_id)
    {
        $phql = "SELECT COUNT(*) AS TOTAL FROM CompanyRatings
                 WHERE company_id = ?0";
        $data = $this->modelsManager->executeQuery($phql,array(0=>$company_id));
        return $data[0]->total;
    }
    public function listReviews($company_id)
    {
        $phql = "SELECT uat.profile_pic,CONCAT(uat.fname,' ',uat.lname) as fullname,cr.id,cr.company_id,cr.user_id,cr.reviews,cr.date_created,cr.rate
                 FROM CompanyRatings cr
                 LEFT JOIN ClientInfo ci ON ci.id = cr.company_id
                 LEFT JOIN UserAccountTb uat ON uat.id = cr.user_id
                 WHERE company_id = ?0
                 ORDER BY cr.date_created DESC";
        $data = $this->modelsManager->executeQuery($phql,array(0=>$company_id));
        return $data;
    }
 

    public function isValid($user_id,$company_id)
    {
        $CURR_DATE = CURR_DATE;
        $date=date_create(CURR_DATE);
        date_sub($date,date_interval_create_from_date_string("3 days"));
        $FROM = date_format($date,"Y-m-d");
       
        $phql = "SELECT COUNT(*) as total FROM CompanyRatings cr 
                 WHERE company_id = ?1 AND user_id = ?0 AND cr.date_created BETWEEN ?2  AND NOW()";
        $data = $this->modelsManager->executeQuery($phql,array(0=>$user_id,1=>$company_id,2=>$FROM));
        return ($data[0]->total > 3) ? true : false;
        
    }
}
